package com.task.controller;

import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.text.SimpleDateFormat;
import java.util.HashMap;
import java.util.Map;

public class ExcelReader {

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelReader.class);

    public static Map<Integer, String> readExcel(String filePath) {
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");

        // 同时支持Excel 2003、2007
        File excelFile = new File(filePath); // 创建文件对象
        Map<Integer, String> map = new HashMap<Integer, String>();
        try {
            FileInputStream is = new FileInputStream(excelFile); // 文件流

            Workbook workbook = WorkbookFactory.create(is); // 这种方式 Excel 2003/2007/2010 都是可以处理的

            int sheetCount = workbook.getNumberOfSheets(); // Sheet的数量
            // 遍历每个Sheet
            for (

                    int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                int rowCount = sheet.getPhysicalNumberOfRows(); // 获取总行数
                // 遍历每一行
                for (int r = 1; r < rowCount; r++) {
                    String var = "";
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); // 获取总列数
                    // 遍历每一列
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        int cellType = cell.getCellType();
                        String cellValue = null;
                        switch (cellType) {
                            case Cell.CELL_TYPE_STRING: // 文本
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_NUMERIC: // 数字、日期
                                if (DateUtil.isCellDateFormatted(cell)) {
                                    cellValue = fmt.format(cell.getDateCellValue()); // 日期型
                                } else {
                                    cellValue = String.valueOf(cell.getNumericCellValue()); // 数字
                                }
                                break;
                            case Cell.CELL_TYPE_BOOLEAN: // 布尔型
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case Cell.CELL_TYPE_BLANK: // 空白
                                cellValue = cell.getStringCellValue();
                                break;
                            case Cell.CELL_TYPE_ERROR: // 错误
                                cellValue = "错误";
                                break;
                            case Cell.CELL_TYPE_FORMULA: // 公式
                                cellValue = "错误";
                                break;
                            default:
                                cellValue = "错误";
                        }
                        var += cellValue + ",";
                    }
                    map.put(r, var);
                }
            }

        } catch (Exception e) {
            LOGGER.error("reader excel file error :{}",e);
        }
        return map;
    }

    public static void main(String[] args) {
        ExcelReader.readExcel("E:\\text.xls");
    }
}
